Inner Join and Full Join

Introduction:

When we are working with databases, SQL (Structured Query Language) provides powerful tools for retrieving data from multiple tables. Two commonly used types of joins are Inner Join and Full Join, each serving distinct purposes in fetching data from related tables.

Inner Join:

An Inner Join is used to retrieve rows from both tables that have matching values based on a specified condition. It merges data from two tables, displaying only the rows that meet the given criteria.

Syntax:

SELECT column1, column2...

FROM table1

INNER JOIN table2 ON table1.column_name = table2.column_name;

Let's illustrate this with a practical example. Consider a simple scenario with two tables: 'employees' and 'departments'.

Employees Table:

Departments Table:

Now, suppose you want to retrieve the names of employees along with their department names. You can achieve this using an Inner Join:

SELECT employees.emp_name, departments.dept_name

FROM employees

INNER JOIN departments ON employees.emp_dept_id = departments.dept_id;

Result:

Here, the Inner Join fetched only the rows where 'emp_dept_id 'from the 'employees' table matches 'dept_id' from the 'departments' table.

Full Join:

A Full Join (also known as a Full Outer Join) retrieves all rows from both tables and combines them based on the specified condition. It includes matching rows as well as unmatched rows from both tables.

Syntax:

SELECT column1, column2...

FROM table1

FULL JOIN table2 ON table1.column_name = table2.column_name;

Let's modify our example to demonstrate a Full Join. Assume we have some additional data in the 'departments' table:

Departments Table (with additional data):

Now, let's perform a Full Join between the 'employees' and 'departments' tables:

SELECT employees.emp_name, departments.dept_name

FROM employees

FULL JOIN departments ON employees.emp_dept_id = departments.dept_id;

Result:

In the Full Join result, it includes all rows from both tables. Rows without a match display 'NULL' values in columns from the opposite table.

Understanding Inner Join and Full Join in SQL provides a powerful way to merge and retrieve data from related tables based on specified conditions, allowing for more comprehensive data analysis and manipulation.